﻿using JNPF.Basics.Models;
using JNPF.Data;
using JNPF.Data.Util;
using JNPF.Utils;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Text;
using System.Threading.Tasks;

namespace JNPF.Basics.Logic
{
    /// <summary>
    /// 数据管理
    /// 版 本：V3.0.0
    /// 版 权：引迈信息技术有限公司（https://www.jnpfsoft.com）
    /// 作 者：JNPF开发平台组
    /// 日 期：2018.02.03
    /// </summary>
    internal class DatabaseService : DataFactory, DatabaseIService
    {
        /// <summary>
        /// 表列表
        /// </summary>
        /// <param name="link">数据连接</param>
        /// <returns></returns>
        public async Task<List<DbTableModel>> GetList(DbLinkEntity link)
        {
            try
            {
                var dbType = link == null ? ConfigurationHelper.GetDbConnection("JNPF_DB_Default:ConnectionType").ToDatabaseType() : link.DbType.ToDatabaseType();
                var db = GetDatabase(link);
                using (db)
                {
                    var strSql = DbSqlBuilder.DBTableSql(dbType);
                    return await db.GetListSqlAsync<DbTableModel>(strSql);
                }
            }
            catch (Exception ex)
            {
                LogFactory.GetLogger().Error(ex);
                throw;
            }
        }
        /// <summary>
        /// 表字段
        /// </summary>
        /// <param name="link">数据连接</param>
        /// <param name="table">表名</param>
        /// <returns></returns>
        public async Task<List<DbTableFieldModel>> GetFieldList(DbLinkEntity link, string table)
        {
            var dbType = link == null ? ConfigurationHelper.GetDbConnection("JNPF_DB_Default:ConnectionType").ToDatabaseType() : link.DbType.ToDatabaseType();
            var db = GetDatabase(link);
            using (db)
            {
                var strSql = DbSqlBuilder.DBTableField(dbType);
                return await db.GetListSqlAsync<DbTableFieldModel>(string.Format(strSql, table));
            }
        }
        public List<DbTableFieldModel> GetFieldListByNoAsync(DbLinkEntity link, string table)
        {
            //var dbType = link == null ? DatabaseType.SqlServer : link.DbType.ToDatabaseType();
            //var connectionString = link == null ? ConfigurationHelper.GetDbConnection("JNPF_DB_Default:ConnectionString") : DataHelper.ToConnectionString(dbType, link.Host, link.Port, link.UserName, link.Password, link.ServiceName);
            //var strSql = DbSqlBuilder.DBTableField(dbType);
            //dynamic sqlCon = null;
            //DataHelper.GetConnection(dbType, connectionString, ref sqlCon);
            //switch (dbType)
            //{
            //    case DatabaseType.SqlServer:
            //        var db = this.Data(connectionString, dbType);
            //        using (db)
            //        {
            //            return db.GetListSql<DbTableFieldModel>(string.Format(strSql, table));
            //        }
            //    case DatabaseType.MySql:
            //        var data = GetDbTableModelListByNoAsync<DbTableFieldModel>(string.Format(strSql, table), sqlCon);
            //        return data;
            //    case DatabaseType.Oracle:
            //        return null;
            //    default:
            //        return new List<DbTableFieldModel>();
            //}
            var dbType = link == null ? ConfigurationHelper.GetDbConnection("JNPF_DB_Default:ConnectionType").ToDatabaseType() : link.DbType.ToDatabaseType();
            var db = GetDatabase(link);
            if (table.IsNullOrEmpty())
            {
                return new List<DbTableFieldModel>();
            }
            using (db)
            {
                var strSql = DbSqlBuilder.DBTableField(dbType);
                return db.GetListSql<DbTableFieldModel>(string.Format(strSql, table));
            }
        }

        /// <summary>
        /// 表数据
        /// </summary>
        /// <param name="link">数据连接</param>
        /// <param name="table">表名</param>
        /// <param name="input">请求参数</param>
        /// <returns></returns>
        public async Task<PageResult> GetData(DbLinkEntity link, string table, PageInput input)
        {
            var dbType = link == null ? ConfigurationHelper.GetDbConnection("JNPF_DB_Default:ConnectionType").ToDatabaseType() : link.DbType.ToDatabaseType();
            var db = GetDatabase(link);
            var queryParam = input.queryJson.ToObject();
            StringBuilder dbSql = new StringBuilder();
            dbSql.AppendFormat("SELECT * FROM {0} WHERE 1=1", table);
            if (!queryParam["field"].IsEmpty() && !input.keyword.IsEmpty())
            {
                dbSql.AppendFormat(" AND {0} like '%{1}%'", queryParam["field"].ToString(), input.keyword);
            }
            using (db)
            {
                var strSql = DbSqlBuilder.DBTableField(dbType);
                return await PageHelper.GetPageResultAsync(db, input, string.Format(dbSql.ToString()));
            }
        }
        /// <summary>
        /// 获取表数据
        /// </summary>
        /// <param name="link"></param>
        /// <param name="table"></param>
        /// <param name="input"></param>
        /// <returns></returns>
        public DataTable GetData(DbLinkEntity link, string table)
        {
            var dbType = link == null ? ConfigurationHelper.GetDbConnection("JNPF_DB_Default:ConnectionType").ToDatabaseType() : link.DbType.ToDatabaseType();
            var db = GetDatabase(link);
            StringBuilder dbSql = new StringBuilder();
            dbSql.AppendFormat("SELECT * FROM {0} WHERE 1=1", table);
            using (db)
            {
                var strSql = DbSqlBuilder.DBTableField(dbType);
                return db.GetTableSql(dbSql.ToString());
            }
        }
        /// <summary>
        /// 执行SQL
        /// </summary>
        /// <param name="link">数据连接</param>
        /// <param name="strSql">sql语句</param>
        /// <returns></returns>
        public async Task<int> ExecuteSql(DbLinkEntity link, string strSql)
        {
            var db = GetDatabase(link);
            using (db)
            {
                return await db.ExecuteSqlAsync(strSql);
            }
        }
        /// <summary>
        /// 删除表
        /// </summary>
        /// <param name="link">数据连接</param>
        /// <param name="table">表名</param>
        public async Task Delete(DbLinkEntity link, string table)
        {
            var db = GetDatabase(link);
            using (db)
            {
                await db.ExecuteSqlAsync(string.Format("DROP TABLE {0}", table));
            }
        }

        /// <summary>
        /// 创建表
        /// </summary>
        /// <param name="link">数据连接</param>
        /// <param name="tableModel">表对象</param>
        /// <param name="tableFieldList">字段对象</param>
        public async Task Create(DbLinkEntity link, DbTableModel tableModel, List<DbTableFieldModel> tableFieldList)
        {
            try
            {
                var dbType = link == null ? ConfigurationHelper.GetDbConnection("JNPF_DB_Default:ConnectionType").ToDatabaseType() : link.DbType.ToDatabaseType();
                var db = GetDatabase(link);
                using (db)
                {
                    db.BeginTransaction();
                    switch (dbType)
                    {
                        case DatabaseType.SqlServer:
                            await CreateTableSqlServer(db, tableModel, tableFieldList);
                            break;
                        case DatabaseType.MySql:
                            await CreateTableMySql(db, tableModel, tableFieldList);
                            break;
                        case DatabaseType.Oracle:
                            await CreateTableOracle(db, tableModel, tableFieldList);
                            break;
                        default:
                            break;
                    }
                    await db.CommitTransactionAsync();
                }
            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }
        }
        /// <summary>
        /// 修改表
        /// </summary>
        /// <param name="link">数据连接</param>
        /// <param name="id">主键值</param>
        /// <param name="tableModel">表对象</param>
        /// <param name="tableFieldList">字段对象</param>
        public async Task Update(DbLinkEntity link, string id, DbTableModel tableModel, List<DbTableFieldModel> tableFieldList)
        {
            var tableModelOld = (await this.GetList(link)).Find(x => x.Table == id);
            var tableFieldListOld = await this.GetFieldList(link, tableModelOld.Table);
            try
            {
                var db = GetDatabase(link);
                using (db)
                {
                    db.BeginTransaction();
                    await this.Delete(link, id);
                    await this.Create(link, tableModel, tableFieldList);
                    await db.CommitTransactionAsync();
                }
            }
            catch (Exception ex)
            {
                await this.Create(link, tableModelOld, tableFieldListOld);
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 根据链接获取数据
        /// </summary>
        /// <returns></returns>
        public async Task<DataTable> GetInterFaceData(DbLinkEntity link, string strSql)
        {
            var db = GetDatabase(link);
            UserInfo userInfo = UserProvider.Instance.Get();
            var positionIds = string.Join("','", userInfo.PositionIds);
            var sb = new StringBuilder();
            sb.Append(
                strSql.Replace("@user", "base_user.F_Id =@user").
                Replace("@organize", "base_organize.F_Id =@organize").
                Replace("@department", "base_organize.F_Id =@department").
                Replace("@postion", "base_position.F_Id in (@postion)"));
            var parameters = new List<DbParameter>();
            parameters.Add(DataHelper.CreateParameter("@user", userInfo.UserId));
            parameters.Add(DataHelper.CreateParameter("@organize", userInfo.OrganizeId));
            parameters.Add(DataHelper.CreateParameter("@department", userInfo.OrganizeId));
            parameters.Add(DataHelper.CreateParameter("@postion", positionIds));
            using (db)
            {
                return await db.GetTableSqlByUnconvertAsync(sb.ToString(), parameters.ToArray());
            }
        }

        /// <summary>
        /// 测试数据库连接
        /// </summary>
        /// <param name="link"></param>
        /// <returns></returns>
        public async Task<bool> IsConnection(DbLinkEntity link)
        {
            var dbType = link == null ? ConfigurationHelper.GetDbConnection("JNPF_DB_Default:ConnectionType").ToDatabaseType() : link.DbType.ToDatabaseType();
            var connectionString = link == null ? ConfigurationHelper.GetDbConnection("JNPF_DB_Default:ConnectionString") : DataHelper.ToConnectionString(dbType, link.Host, link.Port, link.UserName, link.Password, link.ServiceName);
            var result = false;
            var sqlCon = DataHelper.GetConnection(dbType, connectionString);
            try
            {
                sqlCon.Open();
                if (sqlCon.State == ConnectionState.Open)
                {
                    result = true;
                }
            }
            catch (Exception ex)
            {
                throw;
            }
            finally
            {
                sqlCon.Close();
            }
            return result;
        }

        #region Method
        /// <summary>
        /// SqlServer创建表单+注释
        /// </summary>
        /// <param name="db">连接Db</param>
        /// <param name="tableModel">表</param>
        /// <param name="tableFieldList">字段</param>
        private async Task CreateTableSqlServer(IDatabase db, DbTableModel tableModel, List<DbTableFieldModel> tableFieldList)
        {
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("CREATE TABLE " + tableModel.Table + " \r\n");
                strSql.Append("(\r\n");
                foreach (var item in tableFieldList)
                {
                    strSql.Append(item.Field + " " + item.DataType);
                    if (item.DataType == "varchar" || item.DataType == "nvarchar" || item.DataType == "decimal")
                        if (item.DataLength=="-1")
                        {
                            strSql.Append("(max) ");
                        }
                        else
                        {
                            strSql.Append("(" + item.DataLength + ") ");
                        }
                    if (item.PrimaryKey == 1)
                    {
                        strSql.Append(" primary key ");
                    }
                    if (item.AllowNull == 0)
                        strSql.Append(" NOT NULL ");
                    else
                        strSql.Append(" NULL ");
                    strSql.Append(",");
                }
                strSql.Remove(strSql.Length - 1, 1);
                strSql.Append("\r\n");
                strSql.Append(")\r\n\r\n");
                await db.ExecuteSqlAsync(strSql.ToString());

                StringBuilder strSql1 = new StringBuilder();
                strSql1.Append("declare @CurrentUser sysname\r\n");
                strSql1.Append("select @CurrentUser = SCHEMA_NAME()\r\n");
                strSql1.Append("execute sp_addextendedproperty 'MS_Description', '" + tableModel.TableName + "','user', @CurrentUser, 'table', '" + tableModel.Table + "'\r\n");
                await db.ExecuteSqlAsync(strSql1.ToString());
                foreach (var item in tableFieldList)
                {
                    StringBuilder strSql2 = new StringBuilder();
                    strSql2.Append("declare @CurrentUser sysname\r\n");
                    strSql2.Append("select @CurrentUser = SCHEMA_NAME()\r\n");
                    strSql2.Append("execute sp_addextendedproperty 'MS_Description', '" + item.FieldName + "', 'user', @CurrentUser, 'table', '" + tableModel.Table + "', 'column', '" + item.Field + "'\r\n");
                    await db.ExecuteSqlAsync(strSql2.ToString());
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        /// <summary>
        /// MySql创建表单+注释
        /// </summary>
        /// <param name="db">连接Db</param>
        /// <param name="tableModel">表</param>
        /// <param name="tableFieldList">字段</param>
        private async Task CreateTableMySql(IDatabase db, DbTableModel tableModel, List<DbTableFieldModel> tableFieldList)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("CREATE TABLE `" + tableModel.Table + "` (\r\n");
            foreach (var item in tableFieldList)
            {

                strSql.Append("  `" + item.Field + "` " + item.DataType.ToUpper() + "");
                if (item.DataType == "varchar" || item.DataType == "nvarchar" || item.DataType == "decimal")
                    strSql.Append(" (" + item.DataLength + ") ");
                if (item.PrimaryKey == 1)
                {
                    strSql.Append(" primary key ");
                }
                if (item.AllowNull == 0)
                    strSql.Append(" NOT NULL ");
                else
                    strSql.Append(" NULL ");
                strSql.Append("COMMENT '" + item.FieldName + "'");
                strSql.Append(",");
                strSql.Append("\r\n");
            }
            strSql.Append("\r\n");
            strSql.Append(") COMMENT = '" + tableModel.TableName + "';");
            await db.ExecuteSqlAsync(strSql.ToString());
        }
        /// <summary>
        /// Oracle创建表单+注释
        /// </summary>
        /// <param name="db">连接Db</param>
        /// <param name="tableModel">表</param>
        /// <param name="tableFieldList">字段</param>
        private async Task CreateTableOracle(IDatabase db, DbTableModel tableModel, List<DbTableFieldModel> tableFieldList)
        {
            try
            {
                #region 创建表
                StringBuilder strSql = new StringBuilder();
                strSql.Append("CREATE TABLE " + tableModel.Table + " \r\n");
                strSql.Append("(\r\n");
                foreach (var item in tableFieldList)
                {
                    item.DataType = DataTypeConversion(item.DataType, db.dbContext.dbType);
                    strSql.Append(item.Field + " " + item.DataType);
                    if (item.DataType == "VARCHAR" || item.DataType.ToLower() == "nvarchar")
                        strSql.Append("(" + item.DataLength + ") ");
                    if (item.PrimaryKey == 1)
                    {
                        strSql.Append(" primary key ");
                    }
                    if (item.AllowNull == 0)
                        strSql.Append(" NOT NULL ");
                    else
                        strSql.Append(" NULL ");
                    strSql.Append(",");
                }
                strSql.Remove(strSql.Length - 1, 1);
                strSql.Append("\r\n");
                strSql.Append(")\r\n\r\n");
                await db.ExecuteSqlAsync(strSql.ToString());
                #endregion

                #region 添加注释
                StringBuilder strSql1 = new StringBuilder();
                strSql1.AppendFormat("comment on table {0} is '{1}';\r\n", tableModel.Table.ToUpper(), tableModel.TableName);
                await db.ExecuteSqlAsync(strSql1.ToString());
                foreach (var item in tableFieldList)
                {
                    var strSql2 = new StringBuilder();
                    strSql2.AppendFormat("comment on column {0}.{1} is '{2}';\r\n", tableModel.Table.ToUpper(), item.Field, item.FieldName);
                    await db.ExecuteSqlAsync(strSql2.ToString());
                }
                #endregion
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        /// <summary>
        /// 根据链接对象链接数据库
        /// </summary>
        /// <param name="link"></param>
        /// <returns></returns>
        private IDatabase GetDatabase(DbLinkEntity link)
        {
            var dbType = link == null ? ConfigurationHelper.GetDbConnection("JNPF_DB_Default:ConnectionType").ToDatabaseType() : link.DbType.ToDatabaseType();
            var connectionString = link == null ? ConfigurationHelper.GetDbConnection("JNPF_DB_Default:ConnectionString") : DataHelper.ToConnectionString(dbType, link.Host, link.Port, link.UserName, link.Password, link.ServiceName);
            var db = link == null ? this.Basic : this.Data(connectionString, dbType);
            return db;
        }

        /// <summary>
        /// 数据库数据类型转换
        /// </summary>
        /// <param name="dataType"></param>
        /// <param name="databaseType"></param>
        /// <returns></returns>
        private string DataTypeConversion(string dataType, DatabaseType databaseType)
        {
            if (databaseType.Equals(DatabaseType.Oracle))
            {
                switch (dataType)
                {
                    case "text":
                        return "LONG";
                    case "decimal":
                        return "NUMBER(6,2)";
                    case "datetime":
                        return "DATE";
                    case "bigint":
                        return "NUMBER";
                    default:
                        return dataType.ToUpper();
                }
            }
            else if (databaseType.Equals(DatabaseType.MySql))
            {
                return dataType;
            }
            else
            {
                return dataType;
            }
        }

        #endregion
    }
}
